package forum.dao.mysql;

import forum.entities.Category;
import forum.dao.CategoryDAO;
import forum.dao.util.MySQLUtil;

import java.math.BigInteger;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class MySQLCategoryDAO implements CategoryDAO {
    private static final String CREATE_QUERY =
            "INSERT INTO categories(name,parentCategoryId,authorId) VALUES(NULL,NULL,NUll)";
    private static final String FIND_CATEGORY_BY_ID_QUERY =
            "SELECT * FROM categories WHERE id = ?";
    private static final String UPDATE_QUERY =
            "UPDATE categories SET name = ?, parentCategoryId = ?, authorId = ? WHERE id = ?";
    private static final String DELETE_QUERY =
            "DELETE FROM categories WHERE id = ?";
    private static final String FIND_CATEGORY_BY_PARENT_CATEGORY_ID =
            "SELECT * FROM categories WHERE parentCategoryId = ?";
    private static final String FIND_ROOT_CATEGORY_QUERY =
            "SELECT * FROM categories WHERE parentCategoryId is NULL";

    private static ConnectionPool connectionPool = null;
    static {
        String createTableQuery = "CREATE TABLE categories " +
                "(id BIGINT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(50), " +
                "parentCategoryId BIGINT(20), " +
                "authorId BIGINT(20))";
        String createRootCategory = "REPLACE INTO categories(name,parentCategoryId,authorId) " +
                "VALUES('root',NULL,NUll)";
        Connection connection = null;
        PreparedStatement createTableStatement = null;
        PreparedStatement createRootCatStatement = null;
        PreparedStatement checkTableStatement = null;
        ResultSet rs = null;
        try {
            connectionPool = C3P0ConnectionPool.getInstance();
            connection = connectionPool.getConnection();
            checkTableStatement = MySQLUtil.prepareStatement(connection,"SHOW TABLES",false);
            rs = checkTableStatement.executeQuery();
            boolean tableExists = false;
            while (rs.next()) {
                if (rs.getString(1).equals("categories")) {
                    tableExists = true;
                }
            }
            if (!tableExists) {
                createTableStatement = connection.prepareStatement(createTableQuery);
                createTableStatement.executeUpdate();
                createRootCatStatement = connection.prepareStatement(createRootCategory);
                createRootCatStatement.executeUpdate();
            }
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(createTableStatement);
            MySQLUtil.close(connection);
            MySQLUtil.close(createRootCatStatement);
            MySQLUtil.close(checkTableStatement);
        }
    }


    @Override
    public Category create() {
        Category category = null;
        Connection connection = null;
        PreparedStatement createCategoryStatement = null;
        ResultSet generatedKey = null;
        try {
            connection = connectionPool.getConnection();
            createCategoryStatement = MySQLUtil.prepareStatement(connection, CREATE_QUERY, true);
            createCategoryStatement.executeUpdate();
            generatedKey = createCategoryStatement.getGeneratedKeys();
            if (!generatedKey.next()) {
                // TODO Create DAOException class and add DAOException to GenericDAO methods signature.
                // throw new DAOException("No generated key obtained");
            }
            category = new Category(BigInteger.valueOf(generatedKey.getLong(1)));
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();
        } finally {
            MySQLUtil.close(generatedKey);
            MySQLUtil.close(createCategoryStatement);
            MySQLUtil.close(connection);
        }
        return category;
    }

    @Override
    public Category read(BigInteger id) {
        Category category = null;
        Connection connection = null;
        PreparedStatement findCategoryByIdStatement = null;
        ResultSet rs = null;
        try {
            connection = connectionPool.getConnection();
            findCategoryByIdStatement = MySQLUtil.prepareStatement(connection, FIND_CATEGORY_BY_ID_QUERY, false, id);
            rs = findCategoryByIdStatement.executeQuery();
            if (!rs.next()) {
                return null;
            }
            category = new Category(BigInteger.valueOf(rs.getLong("id")),
                    rs.getString("name"),BigInteger.valueOf(rs.getLong("parentCategoryId")),
                    BigInteger.valueOf(rs.getLong("authorId")));
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findCategoryByIdStatement);
            MySQLUtil.close(connection);
        }
        return category;
    }

    @Override
    public void update(Category category) {
        Object [] fields = {
                category.getCategoryName(),
                category.getParentCategoryId(),
                category.getAuthorId(),
                category.getId()
        };
        Connection connection = null;
        PreparedStatement updateCategoryStatement = null;
        try {
            connection = connectionPool.getConnection();
            updateCategoryStatement = MySQLUtil.prepareStatement(connection, UPDATE_QUERY, false, fields);
            updateCategoryStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(updateCategoryStatement);
            MySQLUtil.close(connection);
        }
    }

    @Override
    public void delete(BigInteger id) {
        // TODO Complete the function.
        // This function deletes only the category, but not it contents.
        Connection connection = null;
        PreparedStatement deleteCategoryStatement = null;
        try {
            connection = connectionPool.getConnection();
            deleteCategoryStatement = MySQLUtil.prepareStatement(connection, DELETE_QUERY, false, id);
            deleteCategoryStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(deleteCategoryStatement);
            MySQLUtil.close(connection);
        }
    }

    @Override
    public List<Category> findCategoriesByParentCategoryId(BigInteger parentCategoryId) {
        Connection connection = null;
        PreparedStatement findChildCategoriesStatement = null;
        ResultSet rs = null;
        LinkedList<Category> foundCategories = new LinkedList<Category>();
        try {
            connection = connectionPool.getConnection();
            findChildCategoriesStatement =
                    MySQLUtil.prepareStatement(connection, FIND_CATEGORY_BY_PARENT_CATEGORY_ID, false, parentCategoryId);
            rs = findChildCategoriesStatement.executeQuery();
            while (rs.next()) {
                foundCategories.addLast(new Category(BigInteger.valueOf(rs.getLong("id")),rs.getString("name"),
                        parentCategoryId,BigInteger.valueOf(rs.getLong("authorId"))) );
            }
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findChildCategoriesStatement);
            MySQLUtil.close(connection);
        }
        return foundCategories;
    }

    @Override
    public Category getRootCategory() {
        Category rootCategory = null;
        Connection connection = null;
        PreparedStatement findRootCategoryStatement = null;
        ResultSet rs = null;
        try {
            connection = connectionPool.getConnection();
            findRootCategoryStatement = MySQLUtil.prepareStatement(connection,
                    FIND_ROOT_CATEGORY_QUERY, false);
            rs = findRootCategoryStatement.executeQuery();
            if (!rs.next()) {
                // TODO Create DAOException class and add DAOException to GenericDAO methods signature.
                // throw new DAOException("No generated key obtained");
            }
            rootCategory = new Category(BigInteger.valueOf(rs.getLong("id")),
                    rs.getString("name"),null,null);
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findRootCategoryStatement);
            MySQLUtil.close(connection);
        }
        return rootCategory;
    }
}
